package com.oner365.test.generator.sql;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.oner365.test.cast.CastTest;

public class GeneratorSqlBiz {

  private static final Logger LOGGER = LoggerFactory.getLogger(CastTest.class);

  public static void main(String[] args) throws IOException {
    List<String> dataList = getData();
    dataList.stream().forEach(s -> LOGGER.info(s));
    List<String> deviceList = getDeviceInfo();
    deviceList.stream().forEach(s -> LOGGER.info(s));
    List<String> sqlList = generatorDeviceSql(deviceList, dataList);
    sqlList.stream().forEach(s -> LOGGER.info(s));
    FileUtils.writeLines(new File("src/main/java/com/oner365/test/generator/sql/new_devicei_biz.sql"), sqlList);
  }

  public static List<String> generatorDeviceSql(List<String> deviceList, List<String> dataList) throws IOException {
    String insertSql = "INSERT INTO pfyh.t_device (id,userid,shortudid,udid,idfa,imei,imsi,buytime,warranty,devicetype,type,relationship,devicename,devicenumber,initsiminfo,initsdserialnum,iccid,serialnum,manufacturer,model,os,versionum,phonecode,cpu,ram,romcapacity,romavailablecapacity,sdcapacity,sdavailablecapacity,sdserialnum,camera,wifimac,bluetoothmac,powerstatus,boottime,capacity,capacitystatus,availablecapacity,ip,encryptionlevel,rootflag,lastonlinetime,onlineflag,jailbreakflag,status,registtime,activatetime,token,pushmagic,unlocktoken,challenge,sendregistnoticeflag,allowactrootorjailbreakflag,remark,roamingflag,updatetime,apnstoken,lostflag,dataencryptionflag,specialtypeflag,knoxstatus,productname,offlinestatus,offlinethreshold,emmloginagain,hostid,usagestats,flowupdate,flowquota,clientversion,bitlocker,meid,resolution,oscoreversion,ossoftwareversion,safetyosversion,patchlevel,iccid2,imsi2,networktype,wlanadapterchip,btadapterchip,nfcchip,locatorchip,clientbuildnum,cpuratio,memoryratio,storageratio,bluetoothstate,wifistate,camerastate,microphonestate,mobiledatastate,apn,buildnumber,gpsstate,syncflag,dualstatus,simoper,tpmreport,systemintegrity,idp,idpdeviceid,apptype,subgroupid,orgcode,loginid,maintainflag,screenpwd,want_changed_pwd,label,eraseflag) VALUES ";
    List<String> list = new ArrayList<String>();
    AtomicInteger index = new AtomicInteger(0);
    dataList.stream().forEach(data -> {
      JSONObject json = JSON.parseObject(data);
      JSONArray array = json.getJSONArray("orgId");
      for (int i = 0; i < array.size(); i++) {
        String groupId = array.getString(i);
        String value = deviceList.get(index.incrementAndGet());
        StringBuilder sql = new StringBuilder();
        sql.append(insertSql);
        value = value.replace("{userId}", json.getString("userId"));
        value = value.replace("{groupId}", groupId);
        value = value.replace("{status}", "2");
//        value = value.replace("{appType}", "M");
        value = value.replaceAll("STR_TO_DATE\\(", "");
        value = value.replaceAll(",'%Y-%m-%d %H:%i:%s'\\)", "");
        sql.append("(");
        sql.append(value);
        sql.append(");");
        list.add(sql.toString());
      }
    });
    list.removeIf(Objects::isNull);
    return list;
  }

  public static List<String> getData() throws IOException {
    String filePath = "src/main/java/com/oner365/test/generator/sql/user_biz";
    File file = new File(filePath);
    List<String> list = FileUtils.readLines(file, "UTF-8");
    return list;
  }

  public static List<String> getDeviceInfo() throws IOException {
    String filePath = "src/main/java/com/oner365/test/generator/sql/device_old.sql";
    File file = new File(filePath);
    AtomicInteger index = new AtomicInteger(90000000);
    List<String> list = FileUtils.readLines(file, "UTF-8");
    List<String> deviceList = list.stream().map(s -> {
      if (!s.contains("INSERT INTO") && !";".equals(s) && s != null) {
        int start = 1;
        if (s.startsWith(",")) {
          start = 2;
        }
        String str = StringUtils.substring(s, start, s.lastIndexOf(")"));
        String[] data = str.split(",");
        data[0] = "" + index.incrementAndGet();
        data[1] = "{userId}";
        data[44] = "{status}";
        data[data.length - 8] = "{groupId}";
//        data[data.length - 9] = "{appType}";
        StringBuilder device = new StringBuilder();
        for (String d : data) {
          device.append(d + ",");
        }
        if("NULL".equals(data[12])|| "''".equals(data[12]) || "2".equals(data[10])||"''".equals(data[data.length - 9])) {
          return null;
        }
        return device.substring(0, device.toString().length() - 1);
      }
      return null;
    }).collect(Collectors.toList());
    deviceList.removeIf(Objects::isNull);
    return deviceList;
  }

}
